const sqlite3 = require('sqlite3').verbose()
const path = require('path')
const { app } = require('electron')

class Database {
  constructor() {
    // 数据库文件路径
    const userDataPath = app.getPath('userData')
    this.dbPath = path.join(userDataPath, 'consumption.db')
    this.db = null
  }

  async initialize() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          console.error('数据库连接失败:', err)
          reject(err)
        } else {
          console.log('数据库连接成功')
          this.createTables().then(resolve).catch(reject)
        }
      })
    })
  }

  async createTables() {
    const tables = [
      // 分类表
      `CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        color TEXT DEFAULT '#3498db',
        icon TEXT DEFAULT 'shopping-cart',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      // 消费记录表
      `CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        amount REAL NOT NULL,
        description TEXT NOT NULL,
        category_id INTEGER,
        date DATE NOT NULL,
        payment_method TEXT DEFAULT 'cash',
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories (id)
      )`,
      
      // 预算表
      `CREATE TABLE IF NOT EXISTS budgets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        category_id INTEGER,
        amount REAL NOT NULL,
        period TEXT NOT NULL, -- 'monthly', 'weekly', 'yearly'
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories (id)
      )`,
      
      // 智能建议缓存表
      `CREATE TABLE IF NOT EXISTS ai_suggestions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        type TEXT NOT NULL, -- 'category', 'analysis', 'report'
        input_hash TEXT NOT NULL,
        suggestion TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`
    ]

    for (const sql of tables) {
      await this.run(sql)
    }

    // 插入默认分类
    await this.insertDefaultCategories()
  }

  async insertDefaultCategories() {
    const defaultCategories = [
      { name: '餐饮', color: '#e74c3c', icon: 'utensils' },
      { name: '交通', color: '#3498db', icon: 'car' },
      { name: '购物', color: '#9b59b6', icon: 'shopping-bag' },
      { name: '娱乐', color: '#f39c12', icon: 'gamepad' },
      { name: '医疗', color: '#2ecc71', icon: 'heartbeat' },
      { name: '教育', color: '#34495e', icon: 'graduation-cap' },
      { name: '住房', color: '#95a5a6', icon: 'home' },
      { name: '其他', color: '#7f8c8d', icon: 'ellipsis-h' }
    ]

    for (const category of defaultCategories) {
      try {
        await this.run(
          'INSERT OR IGNORE INTO categories (name, color, icon) VALUES (?, ?, ?)',
          [category.name, category.color, category.icon]
        )
      } catch (err) {
        console.error('插入默认分类失败:', err)
      }
    }
    console.log('默认分类插入完成')
  }

  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) {
          reject(err)
        } else {
          resolve({ id: this.lastID, changes: this.changes })
        }
      })
    })
  }

  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) {
          reject(err)
        } else {
          resolve(row)
        }
      })
    })
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          reject(err)
        } else {
          resolve(rows)
        }
      })
    })
  }

  // 消费记录相关方法
  async getExpenses(filters = {}) {
    let sql = `
      SELECT e.*, c.name as category_name, c.color as category_color, c.icon as category_icon
      FROM expenses e
      LEFT JOIN categories c ON e.category_id = c.id
      WHERE 1=1
    `
    const params = []

    if (filters.startDate) {
      sql += ' AND e.date >= ?'
      params.push(filters.startDate)
    }

    if (filters.endDate) {
      sql += ' AND e.date <= ?'
      params.push(filters.endDate)
    }

    if (filters.categoryId) {
      sql += ' AND e.category_id = ?'
      params.push(filters.categoryId)
    }

    if (filters.minAmount) {
      sql += ' AND e.amount >= ?'
      params.push(filters.minAmount)
    }

    if (filters.maxAmount) {
      sql += ' AND e.amount <= ?'
      params.push(filters.maxAmount)
    }

    sql += ' ORDER BY e.date DESC, e.created_at DESC'

    if (filters.limit) {
      sql += ' LIMIT ?'
      params.push(filters.limit)
    }

    return await this.all(sql, params)
  }

  async addExpense(expense) {
    const sql = `
      INSERT INTO expenses (amount, description, category_id, date, payment_method, notes)
      VALUES (?, ?, ?, ?, ?, ?)
    `
    const params = [
      expense.amount,
      expense.description,
      expense.categoryId,
      expense.date,
      expense.paymentMethod || 'cash',
      expense.notes || ''
    ]
    
    const result = await this.run(sql, params)
    return await this.get('SELECT * FROM expenses WHERE id = ?', [result.id])
  }

  async updateExpense(id, expense) {
    const sql = `
      UPDATE expenses 
      SET amount = ?, description = ?, category_id = ?, date = ?, 
          payment_method = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `
    const params = [
      expense.amount,
      expense.description,
      expense.categoryId,
      expense.date,
      expense.paymentMethod,
      expense.notes,
      id
    ]
    
    await this.run(sql, params)
    return await this.get('SELECT * FROM expenses WHERE id = ?', [id])
  }

  async deleteExpense(id) {
    const result = await this.run('DELETE FROM expenses WHERE id = ?', [id])
    return result.changes > 0
  }

  // 分类相关方法
  async getCategories() {
    return await this.all('SELECT * FROM categories ORDER BY name')
  }

  async addCategory(category) {
    const sql = 'INSERT INTO categories (name, color, icon) VALUES (?, ?, ?)'
    const params = [category.name, category.color || '#3498db', category.icon || 'tag']
    
    const result = await this.run(sql, params)
    return await this.get('SELECT * FROM categories WHERE id = ?', [result.id])
  }

  // 统计相关方法
  async getStatistics(period = 'month') {
    const now = new Date()
    let startDate, endDate

    switch (period) {
      case 'week':
        startDate = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 7)
        endDate = now
        break
      case 'month':
        startDate = new Date(now.getFullYear(), now.getMonth(), 1)
        endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0)
        break
      case 'year':
        startDate = new Date(now.getFullYear(), 0, 1)
        endDate = new Date(now.getFullYear(), 11, 31)
        break
      default:
        startDate = new Date(now.getFullYear(), now.getMonth(), 1)
        endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0)
    }

    const [totalExpense, categoryStats, dailyStats] = await Promise.all([
      this.getTotalExpense(startDate, endDate),
      this.getCategoryStatistics(startDate, endDate),
      this.getDailyStatistics(startDate, endDate)
    ])

    return {
      period,
      startDate: startDate.toISOString().split('T')[0],
      endDate: endDate.toISOString().split('T')[0],
      totalExpense,
      categoryStats,
      dailyStats
    }
  }

  async getTotalExpense(startDate, endDate) {
    const sql = 'SELECT SUM(amount) as total FROM expenses WHERE date BETWEEN ? AND ?'
    const result = await this.get(sql, [startDate.toISOString().split('T')[0], endDate.toISOString().split('T')[0]])
    return result.total || 0
  }

  async getCategoryStatistics(startDate, endDate) {
    const sql = `
      SELECT c.name, c.color, c.icon, SUM(e.amount) as total, COUNT(e.id) as count
      FROM expenses e
      JOIN categories c ON e.category_id = c.id
      WHERE e.date BETWEEN ? AND ?
      GROUP BY c.id, c.name, c.color, c.icon
      ORDER BY total DESC
    `
    return await this.all(sql, [startDate.toISOString().split('T')[0], endDate.toISOString().split('T')[0]])
  }

  async getDailyStatistics(startDate, endDate) {
    const sql = `
      SELECT date, SUM(amount) as total
      FROM expenses
      WHERE date BETWEEN ? AND ?
      GROUP BY date
      ORDER BY date
    `
    return await this.all(sql, [startDate.toISOString().split('T')[0], endDate.toISOString().split('T')[0]])
  }

  async close() {
    return new Promise((resolve) => {
      if (this.db) {
        this.db.close((err) => {
          if (err) {
            console.error('关闭数据库时出错:', err)
          } else {
            console.log('数据库连接已关闭')
          }
          resolve()
        })
      } else {
        resolve()
      }
    })
  }
}

module.exports = Database